/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sunspotworld.executor;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Vector;

/**
 * This class provides all the database related operation
 * @author Laili Aidi - aidi@kth.se
 * Condroid Summer CSD 2011 - KTH
 */
public class DataExecutor {
    // This class assumes a mySQL database has been installed on the
    // local host at the default port (3306), has a database called
    // Test and can be manipulated using the default administrator 
    // settings (empty password for user 'root'). The JDBC driver 
    // needed to communicate with this database is specified in JDBC_DRIVER. 
    // If your setup is based on a different database and driver, you'll
    // need to modify these settings appropriately.
    // Obviously, an empty password should never be used for anything more
    // serious than a test setup. 

    private static final String DATABASE_URL = "jdbc:mysql://127.0.0.1:3306/";
    private static final String DATABASE_NAME = "Test";
    private static final String DATABASE_USER = "sunspot";
    private static final String DATABASE_PASSWORD = "0gpittc?";
    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private Statement stmt = null;
    private java.sql.Connection dbCon = null;
    // Name of the database table where we store sensor readings
    private static final String SENSING_TABLE = "DATA_TABLE";
    private static final String SPOT_TABLE = "SENSOR_TABLE";
    private static final String GATEWAY_TABLE = "GATEWAY_TABLE";
    private static final String MANAGEMENT_TABLE = "MANAGEMENT_TABLE";
    /**
     * 
     */
    public final int TIME = 0;
    /**
     * 
     */
    public final int SPOT_ADDR = 1;
    /**
     * 
     */
    public final int MAX_LIGHT = 2;
    /**
     * 
     */
    public final int MIN_LIGHT = 3;
    /**
     * 
     */
    public final int AVG_LIGHT = 4;
    /**
     * 
     */
    public final int MAX_TEMP = 5;
    /**
     * 
     */
    public final int MIN_TEMP = 6;
    /**
     * 
     */
    public final int AVG_TEMP = 7;

    /**
     * 
     * This method is used to connect to the database
     * @return isSuccess
     */
    public boolean connect() {
        try {
            // Register the JDBC driver for mysql. This is typically found in a JAR obFile
            // named something like mysql-connector-java-<version>-bin.jar
            // which should be in the classpath See user.classpath in build.properties for this Sun SPOT host application
            Class.forName(JDBC_DRIVER);
            // Define URL of database server for
            String url = DATABASE_URL + DATABASE_NAME;

            // Display URL and connection information
            System.out.println("Connecting to Database URL: " + url + "Connection: " + dbCon);

            // Get a connection to the database for given user/password
            dbCon = DriverManager.getConnection(url, DATABASE_USER, DATABASE_PASSWORD);
            // Get a Statement object
            stmt = dbCon.createStatement();
            //Create the WSN pool table
            System.out.println("Creating statement and tables..");
            boolean isSucess = createGatewayTable();
            if (isSucess) {
                isSucess = createSpotTable();
                if (isSucess) {
                    isSucess = createSensingTable();
                    if (isSucess) {
                        isSucess = createSensingTable();
                        if (isSucess) {
                            isSucess = createManagementTable();
                        } else {
                            System.err.println("ERROR in DataManager.connect 3: Can't create Management table");
                            return false;
                        }
                    } else {
                        System.err.println("ERROR in DataManager.connect 3: Can't create Sensing table");
                        return false;
                    }
                } else {
                    System.err.println("ERROR in DataManager.connect 2: Can't create Sensor table");
                    return false;
                }
            } else {
                System.err.println("ERROR in DataManager.connect 1: Can't create Gateway table");
                return false;
            }
        } catch (Exception e) {
            System.err.println("ERROR in DataManager.connect: Make sure that mySQL is installed properly \n"
                    + "and has a " + DATABASE_NAME + " database accessible via the \n"
                    + "default administrator settings on " + DATABASE_URL + "\n");
            return false;
        }
        return true;
    }

    /**
     * 
     * This method is used to disconnect from the database
     */
    public void disconnect() {
        try {
            if (dbCon != null) {
                dbCon.close();
            }
        } catch (SQLException se) {
        } catch (Exception e) {
        }
    }

    /*
     * Create management_table that record the current setting of the gateway, consist of 5 columns:
     * management_id padded identifier for MANAGEMENT_TABLE (PRIMARY KEY)
     * gateway_name padded to 100 chars of identifier of the name of this gateway (DTN EID)
     * location padded to 100 chars of location of the GATEWAY
     * receiver_name padded to 100 chars of identifier of the name of receiver gateway (DTN EID)
     * mode padded to 20 chars of the operation mode of the gateway: city (receiver) or village (sender)
     */
    private boolean createManagementTable() {
        try {
            stmt.executeUpdate(
                    "CREATE TABLE " + MANAGEMENT_TABLE
                    + " (management_id MEDIUMINT NOT NULL AUTO_INCREMENT, "
                    + "gateway_name CHAR(100), "
                    + "location CHAR(100), "
                    + "receiver_name CHAR(100), "
                    + "mode CHAR(20), "
                    + "PRIMARY KEY (management_id))");
            System.out.println("Message: New table " + MANAGEMENT_TABLE + " is created.");

            try {
                stmt.executeUpdate("INSERT INTO " + MANAGEMENT_TABLE
                        + "(gateway_name, location, receiver_name, mode) "
                        + "VALUES(\'-\', \'-\', \'-\', \'-\')");
                return true;
            } catch (SQLException e) {
                System.err.println("ERROR Caught createManagementTable 1:" + e.getMessage() + " while storing Management Table");
                return false;
            } catch (Exception e) {
                System.err.println("ERROR Caught createManagementTable 2:" + e.getMessage() + " while writing Management Table.");
                return false;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("Message: table " + MANAGEMENT_TABLE + " is exist");
        }
        return true;
    }

    /*
     * Create GATEWAY_TABLE that record the information of the gateway, consist of 2 columns:
     * gateway_id padded identifier for GATEWAY (PRIMARY KEY)
     * gateway_mac_addr padded to 100 chars of Physical Addr of the GATEWAY
     * location padded to 100 chars of location of the GATEWAY
     */
    private boolean createGatewayTable() {
        try {
            stmt.executeUpdate(
                    "CREATE TABLE " + GATEWAY_TABLE
                    + " (gateway_id MEDIUMINT NOT NULL AUTO_INCREMENT, "
                    + "gateway_mac_addr CHAR(100) NOT NULL, "
                    + "gateway_name CHAR(100), "
                    + "location CHAR(100), "
                    + "PRIMARY KEY (gateway_id))");
            System.out.println("Message: New table " + GATEWAY_TABLE + " is created.");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("Message: table " + GATEWAY_TABLE + " is exist");
        }
        return true;
    }

    /*
     * Create SPOT_TABLE that record the information of the sensor, consist of 4 columns:
     * spot_id padded identifier for SPOT (PRIMARY KEY)
     * gateway_id padded identifier for GATEWAY (FOREIGN KEY)
     * spot_mac_addr padded to 100 chars of Physical Addr of the SPOT
     * location padded to 100 chars of location of the SPOT
     */
    private boolean createSpotTable() {
        try {
            //System.out.println(
            //        "CREATE TABLE " + SPOT_TABLE
            //        + " (spot_id MEDIUMINT NOT NULL AUTO_INCREMENT, "
            //        + " gateway_id MEDIUMINT NOT NULL REFERENCES " + GATEWAY_TABLE + "(gateway_id), "
            //        + "spot_mac_addr CHAR(20) NOT NULL, "
            //        + "location CHAR(100), "
            //        + "PRIMARY KEY (spot_id))");

            stmt.executeUpdate(
                    "CREATE TABLE " + SPOT_TABLE
                    + " (spot_id MEDIUMINT NOT NULL AUTO_INCREMENT, "
                    + " gateway_id MEDIUMINT NOT NULL REFERENCES " + GATEWAY_TABLE + "(gateway_id), "
                    + "spot_mac_addr CHAR(100) NOT NULL, "
                    + "location CHAR(100), "
                    + "PRIMARY KEY (spot_id))");
            System.out.println("Message: New table " + SPOT_TABLE + " is created.");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("Message: table " + SPOT_TABLE + " is exist");
        }
        return true;
    }

    /*
     * Create SENSING_TABLE that record the information of the sensing data at the current time, consist of 8 columns:
     * sensingId padded identifier for DATA (PRIMARY KEY)
     * gateway_id padded identifier for GATEWAY (FOREIGN KEY)
     * SpotId padded identifier for SPOT (FOREIGN KEY)
     * timestamp padded to YYYY-MM-DD hh:mm:ss string when reading was taken
     * light padded ligth sensor reading (4 is simply the display width but an unsigned smallint can store any value from 0 to 65535)
     * temperature padded to double of temperature sensor reading
     * battery padded to integer of battery sensor reading
     * flag padded to integer that identify if this data has been sent as DTN Bundle or not (1 or 0)
     */
    private boolean createSensingTable() {
        try {
            //System.out.println(
            //        "CREATE TABLE " + SENSING_TABLE
            //        + " (sensing_id MEDIUMINT NOT NULL AUTO_INCREMENT, "
            //        + "spot_id MEDIUMINT NOT NULL REFERENCES " + SPOT_TABLE + "(spot_id), "
            //        + "gateway_id MEDIUMINT NOT NULL REFERENCES " + GATEWAY_TABLE + "(gateway_id), "
            //        + "timestamp TIMESTAMP DEFAULT 0, "
            //        + "light SMALLINT(4) UNSIGNED, "
            //        + "temperature DOUBLE,"
            //        + "battery DOUBLE,"
            //        + "flag TINYINT(1), "
            //        + "PRIMARY KEY (sensing_id))");

            stmt.executeUpdate(
                    "CREATE TABLE " + SENSING_TABLE
                    + " (sensing_id MEDIUMINT NOT NULL AUTO_INCREMENT, "
                    + "spot_id MEDIUMINT NOT NULL REFERENCES " + SPOT_TABLE + "(spot_id), "
                    + "gateway_id MEDIUMINT NOT NULL REFERENCES " + GATEWAY_TABLE + "(gateway_id), "
                    + "timestamp TIMESTAMP DEFAULT 0, "
                    + "light SMALLINT(4) UNSIGNED, "
                    + "temperature DOUBLE,"
                    + "battery DOUBLE,"
                    + "flag TINYINT(1), "
                    + "PRIMARY KEY (sensing_id))");
            System.out.println("Message: New table " + SENSING_TABLE + " is created.");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("Message: table " + SENSING_TABLE + " is exist");
        }
        return true;
    }

    /**
     * 
     * Delete the GATEWAY_TABLE  table if left over from a previous run. 
     * If no table exists, an exception will be thrown.
     * @return
     */
    public boolean deleteManagementTable() {
        try {
            // Delete the data collection table if left over
            // from a previous run. If no table exists, an exception will be thrown.
            stmt.executeUpdate("DROP TABLE " + MANAGEMENT_TABLE);
            System.out.println("Message: Existing table " + MANAGEMENT_TABLE + " is deleted.");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("Message: No existing table table " + MANAGEMENT_TABLE + " to delete.");
        }
        return true;
    }

    /**
     * 
     * Delete the GATEWAY_TABLE table if left over from a previous run. 
     * If no table exists, an exception will be thrown.
     * @return
     */
    public boolean deleteGatewayTable() {
        try {
            // Delete the data collection table if left over
            // from a previous run. If no table exists, an exception will be thrown.
            stmt.executeUpdate("DROP TABLE " + GATEWAY_TABLE);
            System.out.println("Message: Existing table " + GATEWAY_TABLE + " is deleted.");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("Message: No existing table table " + GATEWAY_TABLE + " to delete.");
        }
        return true;
    }

    /**
     * 
     * Delete the SPOT_TABLE  table if left over from a previous run. 
     * If no table exists, an exception will be thrown.
     * @return
     */
    public boolean deleteSpotTable() {
        try {
            // Delete the data collection table if left over
            // from a previous run. If no table exists, an exception will be thrown.
            stmt.executeUpdate("DROP TABLE " + SPOT_TABLE);
            System.out.println("Message: Existing table " + SPOT_TABLE + " is deleted.");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("Message: No existing table table " + SPOT_TABLE + " to delete.");
        }
        return true;
    }

    /**
     * 
     * Delete the SENSING_TABLE table if left over from a previous run. 
     * If no table exists, an exception will be thrown.
     * @return
     */
    public boolean deleteSensingTable() {
        try {
            // Delete the data collection table if left over
            // from a previous run. If no table exists, an exception will be thrown.
            stmt.executeUpdate("DROP TABLE " + SENSING_TABLE);
            System.out.println("Message: Existing table " + SENSING_TABLE + " is deleted.");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println("Message: No existing table table " + SENSING_TABLE + " to delete.");
        }
        return true;
    }
    Vector obVectorData;

    /**
     * 
     * This method is used to get the bulk data from the Gateway Table
     * @return Vector of the GATEWAY_TABLE bulk data
     */
    public Vector readGatewayTable() {
        ResultSet obResultSet = null;
        obVectorData = null;

        try {
            obVectorData = new Vector();
            obResultSet = stmt.executeQuery("SELECT * from " + GATEWAY_TABLE);
            // System.out.println("Collected GATEWAY readings:");
            // System.out.println("\t+-------------------------------------------------------+");
            // System.out.println("\t|       gateway_id     \t      gateway_mac_addr     \t      location|");
            // System.out.println("\t+-------------------------------------------------------+");
            while (obResultSet.next()) {
                String[] obStrData = new String[4];
                obStrData[0] = obResultSet.getString("gateway_id");
                obStrData[1] = obResultSet.getString("gateway_mac_addr");
                obStrData[2] = obResultSet.getString("gateway_name");
                obStrData[3] = obResultSet.getString("location");
                System.out.println("\t " + obStrData[0] + "\t" + obStrData[1] + "\t" + "\t" + obStrData[2] + "\t" + obStrData[3]);
                //String obStrText = obStrGatewayId + " " + obStrGatewayAddr + " " + obStrGatewayLocation;
                //Put to the Hashtable;
                obVectorData.add(obStrData);
            }
            // System.out.println("\t+-------------------------------------------------------+");
        } catch (Exception e) {
            System.err.println("ERROR Caught " + e.getMessage() + " in readGatewayTable()");
            obVectorData = null;
        }
        return obVectorData;
    }

    /**
     * 
     * This method is used to set the name of a Gateway with specific input Id
     * @param obStrGatewayAddr
     * @param obStrGatewayName
     * @param obStrGatewayLocation
     * @return isSuccess
     */
    public boolean writeGatewayTable(String obStrGatewayAddr, String obStrGatewayName, String obStrGatewayLocation) {
        if (getGatewayId(obStrGatewayAddr) == -1) { //Means this gateway is not stored yet in DB
            try {
                //System.out.println("INSERT INTO " + GATEWAY_TABLE
                //        + "(gateway_mac_addr, location) "
                //        + "VALUES(\'" + obStrGatewayAddr + "\', \'" + obStrGatewayLocation + "\')");

                stmt.executeUpdate("INSERT INTO " + GATEWAY_TABLE
                        + "(gateway_mac_addr, gateway_name, location) "
                        + "VALUES(\'" + obStrGatewayAddr + "\', \'" + obStrGatewayName + "\', \'" + obStrGatewayLocation + "\')");
                return true;
            } catch (SQLException e) {
                System.err.println("ERROR Caught setGatewayName 1:" + e.getMessage() + " while storing Gateway Table <"
                        + "\'" + obStrGatewayAddr + ">");
                return false;
            } catch (Exception e) {
                System.err.println("ERROR Caught setGatewayName 2:" + e.getMessage() + " while writing Gateway Table.");
                return false;
            }
        }
        return true;
    }

    /**
     * 
     * This method is used to set the Address of a Gateway from specific input Id
     * Input: Gateway Id
     * @param obStrGatewayId
     * @return Gateway Mac Address
     */
    public String[] getGateway(String obStrGatewayId) {
        try {
            ResultSet obResultSet = null;
            obResultSet = stmt.executeQuery("SELECT * FROM " + GATEWAY_TABLE + " WHERE gateway_id = \'" + obStrGatewayId + "\'");
            String[] obGatewayData = new String[3];

            while (obResultSet.next()) {
                obGatewayData[0] = obResultSet.getString("gateway_mac_addr");
                obGatewayData[1] = obResultSet.getString("gateway_name");
                obGatewayData[2] = obResultSet.getString("location");
                return obGatewayData;
            }
        } catch (Exception e) {
            System.err.println("ERROR Caught getSpotAddr 1: " + e.getMessage() + " in getSpotAddr.");
            return null;
        }
        return null;
    }

    /**
     * 
     * This method is used to get the Id of a Gateway from specific input Address
     * @param obStrGatewayAddr
     * @return Gateway Id
     */
    public int getGatewayId(String obStrGatewayAddr) {
        try {
            ResultSet obResultSet = null;
            obResultSet = stmt.executeQuery("SELECT * FROM " + GATEWAY_TABLE + " WHERE gateway_mac_addr = \'" + obStrGatewayAddr + "\'");

            while (obResultSet.next()) {
                return obResultSet.getInt("gateway_id");
            }
        } catch (Exception e) {
            System.err.println("ERROR Caught getSpotId 1: " + e.getMessage() + " in getSpotId.");
            return -1;
        }
        return -1;
    }

    /**
     * This method is used to get the bulk data from the SPOT Table
     * @return Vector of the SPOT_TABLE bulk data
     */
    public Vector readSpotTable() {
        ResultSet obResultSet = null;
        String obStrSpotAddr = null;
        String obStrGatewayID = null;
        String obStrSpotId = null;
        String obStrSpotLocation = null;
        obVectorData = null;

        try {
            obVectorData = new Vector();
            obResultSet = stmt.executeQuery("SELECT * from " + SPOT_TABLE);
            // System.out.println("Collected SPOT readings:");
            // System.out.println("\t+-------------------------------------------------------+");
            // System.out.println("\t|       spot_id     \t     gateway_id     \t      spot_mac_addr     \t      location|");
            // System.out.println("\t+-------------------------------------------------------+");
            while (obResultSet.next()) {
                String[] obStrData = new String[4];
                obStrData[0] = obResultSet.getString("spot_id");
                obStrData[1] = obResultSet.getString("gateway_id");
                obStrData[2] = obResultSet.getString("spot_mac_addr");
                obStrData[3] = obResultSet.getString("location");
                //   System.out.println("\t " + obStrData[0] + "\t" + obStrData[1] + "\t" + obStrData[2] + "\t" + obStrData[3]);
                String obStrText = obStrData[0] + " " + obStrData[1] + " " + obStrData[2] + " " + obStrData[3];
                //Put to the Hashtable;
                obVectorData.add(obStrData);
            }
            // System.out.println("\t+-------------------------------------------------------+");
        } catch (Exception e) {
            System.err.println("ERROR Caught " + e.getMessage() + " in readSpotTable()");
            obVectorData = null;
        }
        return obVectorData;
    }

    /**
     * This method is used to set the Address of a Spot with specific input Id
     * @param obIntGatewayID
     * @param obStrSpotAddr
     * @param obStrSpotLocation
     * @return isSuccess
     */
    public boolean writeSpotTable(int obIntGatewayID, String obStrSpotAddr, String obStrSpotLocation) {
        if (getSpotId(obStrSpotAddr) == -1) {//Means this spot is not stored yet in DB
            try {
                // System.out.println("INSERT INTO " + SPOT_TABLE
                //         + "(gateway_id, spot_mac_addr, location) "
                //         + "VALUES(\'" + obIntGatewayID + "\',\'" + obStrSpotAddr + "\',\'" + obStrSpotLocation + "\')");

                stmt.executeUpdate("INSERT INTO " + SPOT_TABLE
                        + "(gateway_id, spot_mac_addr, location) "
                        + "VALUES(\'" + obIntGatewayID + "\',\'" + obStrSpotAddr + "\',\'" + obStrSpotLocation + "\')");
                return true;
            } catch (SQLException e) {
                System.err.println("ERROR Caught writeSpotTable 1:" + e.getMessage() + " while storing Spot Table <"
                        + "\'" + obStrSpotAddr + ">");
                return false;
            } catch (Exception e) {
                System.err.println("ERROR Caught writeSpotTable 2:" + e.getMessage() + " while writing Spot Table.");
                return false;
            }
        }
        return true;
    }

    /**
     * This method is used to get the Address of a Spot from specific input Id
     * @param obStrSpotId
     * @return ARRAY STRING of Gateway ID, Spot Mac Address and Location
     */
    public String[] getSpot(String obStrSpotId) {
        try {
            ResultSet obResultSet = null;
            obResultSet = stmt.executeQuery("SELECT * FROM " + SPOT_TABLE + " WHERE spot_id = \'" + obStrSpotId + "\'");
            String[] obSpotData = new String[3];

            while (obResultSet.next()) {
                obSpotData[0] = obResultSet.getString("gateway_id");
                obSpotData[1] = obResultSet.getString("spot_mac_addr");
                obSpotData[2] = obResultSet.getString("location");
                return obSpotData;
            }
        } catch (Exception e) {
            System.err.println("ERROR Caught getSpotAddr 1: " + e.getMessage() + " in getSpotAddr.");
            return null;
        }
        return null;
    }

    /**
     * This method is used to get the Receiver name (DTN EID) of this Gateway
     * @return Receiver DTN EID
     */
    public String getReceiverName() {
        try {
            ResultSet obResultSet = null;
            obResultSet = stmt.executeQuery("SELECT * FROM " + MANAGEMENT_TABLE);

            while (obResultSet.next()) {
                return obResultSet.getString("receiver_name");
            }
        } catch (Exception e) {
            System.err.println("ERROR Caught getReceiverName 1: " + e.getMessage() + " in getReceiverName.");
        }
        return null;
    }

    /**
     * This method is used to get the Id of a Spot from specific input Address
     * @param obStrSpotAddr
     * @return Spot Id
     */
    public int getSpotId(String obStrSpotAddr) {
        try {
            ResultSet obResultSet = null;
            obResultSet = stmt.executeQuery("SELECT * FROM " + SPOT_TABLE + " WHERE spot_mac_addr = \'" + obStrSpotAddr + "\'");

            while (obResultSet.next()) {
                return obResultSet.getInt("spot_id");
            }
        } catch (Exception e) {
            System.err.println("ERROR Caught getSpotId 1: " + e.getMessage() + " in getSpotId.");
            return -1;
        }
        return -1;
    }

    /**
     * 
     * This method is used to update the flag that inform if the data has been sent as DTN bundle or not
     * @param obStrSensingID
     * @param obStrTimestap
     * @param obFlag
     * @return isSuccess
     */
    public boolean updateFlag(String obStrSensingID, String obStrTimestap, int obFlag) {
        boolean isSuccess = false;
        try {
            stmt.executeUpdate("UPDATE " + SENSING_TABLE + " SET flag = \'" + obFlag + "\' WHERE sensing_id = \'" + obStrSensingID + "\'");;
            isSuccess = true;
        } catch (SQLException sqle) {
            System.err.println("ERROR Caught updateFlag 1: " + sqle.getMessage());
            return isSuccess;
        } catch (Exception e) {
            System.err.println("ERROR Caught updateFlag 2: " + e.getMessage());
            return isSuccess;
        }
        return isSuccess;
    }

    /**
     * This method is used to get the bulk unsent data from the SENSING_TABLE
     * @return Vector of the SPOT_TABLE bulk data
     */
    public Vector readSensingTableUnsent() {
        ResultSet obResultSet = null;
        String obStrSensingID = null;
        String obStrSpotID = null;
        String obStrGatewayID = null;
        String obStrTimeStamp = null;
        int obIntLight = 0;
        double obDoubleTemp = 0;
        obVectorData = null;

        int obFlag = 0;

        try {
            //Put the data to the hashtable
            obVectorData = new Vector();
            obResultSet = stmt.executeQuery("SELECT * from " + SENSING_TABLE + " WHERE flag = \'" + obFlag + "\'");
            //System.out.println("Collected sensor readings:");
            //System.out.println("\t+-------------------------------------------------------+");
            //System.out.println("\t|       sensing_id     \t      spot_id       \t      gateway_id       \t      timestamp       \t      light       \t      temperature      \t      flag|");
            //System.out.println("\t+-------------------------------------------------------+");
            while (obResultSet.next()) {
                String[] obStrSensingData = new String[7];
                obStrSensingData[0] = obResultSet.getString("sensing_id");
                obStrSensingData[1] = obResultSet.getString("spot_id");
                obStrSensingData[2] = obResultSet.getString("gateway_id");
                obStrSensingData[3] = obResultSet.getString("timestamp");
                obStrSensingData[4] = Integer.toString(obResultSet.getInt("light"));
                obStrSensingData[5] = Double.toString(obResultSet.getDouble("temperature"));
                obStrSensingData[6] = Double.toString(obResultSet.getDouble("battery"));

                //System.out.println("\t       " + obStrSensingData[0] + "     \t      " + obStrSensingData[1] + "       \t      " + obStrSensingData[2]
                //        + "       \t      " + obStrSensingData[3] + "       \t      " + obStrSensingData[4] + "       \t      " + obStrSensingData[5]);

                //Put to the Hashtable;
                obVectorData.add(obStrSensingData);
            }
            //System.out.println("\t+-------------------------------------------------------+");
        } catch (Exception e) {
            System.err.println("ERROR Caught readSensingTableUnsent:" + e.getMessage());
        }
        return obVectorData;
    }

    /**
     * This method is used to check if the sensing data is exist in database or not, to avoid data redundancy in database
     * @param obStrSpotAddr
     * @param obTobStrTimeStampParam
     * @return isExist
     */
    public boolean isSensingExist(String obStrSpotAddr, String obTobStrTimeStampParam) {
        ResultSet obResultSet = null;
        String obStrSpotID = null;
        String obStrTimeStamp = null;

        try {

            obResultSet = stmt.executeQuery("SELECT spot_id from " + SPOT_TABLE + " WHERE spot_mac_addr = \'" + obStrSpotAddr + "\'");

            while (obResultSet.next()) {
                obStrSpotID = obResultSet.getString("spot_id");
                //System.err.println("DATA obStrSpotID " + obStrSpotID);
                break;
            }
            if (obStrSpotID != null && obStrSpotID.length() > 0) {
                obResultSet = stmt.executeQuery("SELECT timestamp from " + SENSING_TABLE + " WHERE spot_id = \'" + obStrSpotID + "\' AND timestamp = \'" + obTobStrTimeStampParam + "\'");
                while (obResultSet.next()) {
                    obStrTimeStamp = obResultSet.getString("timestamp");
                    obStrTimeStamp = obStrTimeStamp.substring(0, obStrTimeStamp.length() - 2);
                    if (obTobStrTimeStampParam.contains(obStrTimeStamp)) {
                        //MEANS THIS DATA IS ALREADY IN THE TABLE
                        return false;
                    }
                }
            }

        } catch (Exception e) {
            System.err.println("ERROR Caught readSensingTable:" + e.getMessage());
        }
        return true;
    }

    /**
     * This method is used to write new sensing data to the database
     * @param obStrGatewayAddr
     * @param obStrGatewayName
     * @param obStrGatewayLoc
     * @param obStrSpotAddr
     * @param obStrSpotLoc
     * @param obTimeStamp
     * @param obIntLight
     * @param obDoubleTemp
     * @param obDoubleBatt
     * @param obFlag
     * @return isSuccess
     */
    public boolean writeSensingTable(String obStrGatewayAddr, String obStrGatewayName, String obStrGatewayLoc, String obStrSpotAddr, String obStrSpotLoc, String obTimeStamp, int obIntLight, double obDoubleTemp, double obDoubleBatt, int obFlag) {
        boolean isSuccess = false;
        try {
            //Check if this Gateway is in the table or not
            int obIntGatewayID = getGatewayId(obStrGatewayAddr);
            if (obIntGatewayID == -1) {
                //This Gateway Name is not in the table, thus we insert it to the table, and get the Id
                isSuccess = writeGatewayTable(obStrGatewayAddr, obStrGatewayName, obStrGatewayLoc);
                if (isSuccess) {
                    obIntGatewayID = getGatewayId(obStrGatewayAddr);
                } else {
                    return false;
                }
            }

            //Check if this Spot is in the table or not
            int obIntSpotID = getSpotId(obStrSpotAddr);
            if (obIntSpotID == -1) {
                //This spot Addr is not in the table, thus we insert it to the table, and get the Id
                isSuccess = writeSpotTable(obIntGatewayID, obStrSpotAddr, obStrSpotLoc);
                if (isSuccess) {
                    obIntSpotID = getSpotId(obStrSpotAddr);
                } else {
                    return false;
                }
            }

            //System.out.println("INSERT INTO " + SENSING_TABLE
            //        + "(spot_id, gateway_id, timestamp, light, temperature, flag)"
            //        + " VALUES(\'" + obIntSpotID + "\', \'" + obIntGatewayID + "\', \'" + obTimeStamp + "\', \'" + obIntLight + "\', \'" + obDoubleTemp + "\', \'" + obFlag + "\')");

            stmt.executeUpdate("INSERT INTO " + SENSING_TABLE
                    + "(spot_id, gateway_id, timestamp, light, temperature, battery, flag)"
                    + " VALUES(\'" + obIntSpotID + "\', \'" + obIntGatewayID + "\', \'" + obTimeStamp + "\', \'" + obIntLight + "\', \'" + obDoubleTemp + "\', \'" + obDoubleBatt + "\', \'" + obFlag + "\')");
            return true;
        } catch (SQLException sqle) {
            System.err.println("ERROR Caught writeSensingTable 1: " + sqle.getMessage() + " while writing sensor sample <"
                    + "\'" + obStrSpotAddr + "\', \'" + obStrGatewayAddr + "\', \'" + obTimeStamp + "\', \'" + obIntLight + "\', \'" + obDoubleTemp + "\', \'" + obDoubleBatt + "\', \'" + obFlag + ">");
            return false;
        } catch (Exception e) {
            System.err.println("ERROR Caught writeSensingTable 2: " + e.getMessage() + " while writing sensor samples to the database.");
            return false;
        }
    }
}
